package net.droiding.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.naming.InitialContext;
import javax.sql.DataSource;

import net.droiding.server.pojo.Provider;
import net.droiding.server.pojo.ProviderKey;
import net.droiding.server.pojo.ProviderType;
import net.droiding.server.pojo.Station;
import net.droiding.server.pojo.StationData;

public class Dao {
	
	private static final String STM_LOGIN = "SELECT PASSWD FROM USERS WHERE EMAIL = ?";
	
	private static final String STM_INSERTSTATION = "INSERT INTO STATIONS "
			+ "     (SKEY, PKEY, LASTUPDATE, LOCATION, LATITUDE, LONGITUDE) "
			+ "VALUES " + "     (?, ?, ?, ?, ?, ?) ";

	private static final String STM_UPDATESTATION = "UPDATE STATIONS "
			+ "     SET LASTUPDATE = ? WHERE SKEY = ? AND PKEY = ? ";

	private static final String STM_INSERTSTATIONDATA = "INSERT INTO STATIONSDATA "
			+ "     (SKEY, PKEY, BIKES, SLOTS, AVAILABLE, LASTUPDATE) "
			+ "VALUES " + "     (?, ?, ?, ?, ?, ?) ";

	private static final String STM_INSERTPROVIDER = "INSERT INTO PROVIDERS "
			+ "     (PKEY, LASTUPDATE, NAME, WEBSITE, TYPE, LOCATION, ZOOMLEVEL, LATITUDE, LONGITUDE) "
			+ "VALUES " + "     (?, ?, ?, ?, ?, ?, ?, ?, ?) ";

	private static final String STM_UPDATEPROVIDER = "UPDATE PROVIDERS SET LASTUPDATE = ? WHERE PKEY = ?";

	private static final String STM_GETPROVIDERBYKEY = "SELECT * FROM PROVIDERS WHERE PKEY = ?";

	private static final String STM_GETALLPROVIDERS = "SELECT * FROM PROVIDERS";
	
	private static final String STM_REFRESH_PROVIDERS = "SELECT * FROM PROVIDERS WHERE LASTUPDATE > ?";
	
	private static final String STM_GETSTATIONBYKEY = "SELECT * FROM STATIONS WHERE SKEY = ? AND PKEY = ?";
	
	private static final String STM_GETSTATIONSBYPROVIDER = "SELECT * FROM STATIONS WHERE PKEY = ?";

	private static final String STM_GETLASTDATA = "SELECT * FROM STATIONSDATA WHERE SKEY = ? AND PKEY = ? ORDER BY LASTUPDATE DESC LIMIT 0, 1";

	private DataSource ds = null;
	
	private Connection conn = null;
	
	private boolean activeTransaction = false;
	
	

	public Dao() {
		try {
			InitialContext cxt = new InitialContext();
			if ( cxt == null ) {
			   throw new Exception("null initial context");
			}
			
			ds = (DataSource) cxt.lookup( "java:/comp/env/jdbc/droiding" );
			
			if ( ds == null ) {
			   throw new Exception("Data source not found!");
			}
			} catch (Throwable t) {
				new RuntimeException("error retrieving datasource !!!");
			}
	}
	public boolean isTxActive() {
		return activeTransaction;
	}
	public void beginTransaction() throws Exception {
		getConnection();
		conn.setAutoCommit(false);
		activeTransaction = true;
	}
	public void commit() throws Exception {
		if (conn == null)
			throw new Exception("no transaction !");
		conn.commit();
		activeTransaction = false;
		releaseConnection();
	}
	public void rollback() throws Exception {
		if (conn == null)
			throw new Exception("no transaction !");
		conn.rollback();
		activeTransaction = false;
		releaseConnection();
	}
	private void releaseConnection() throws Exception {
		if (!activeTransaction && conn != null) {
			conn.close();
			conn = null;
		}
	}
	private void getConnection() throws Exception {
		if (!activeTransaction && conn == null)
			conn = ds.getConnection();
	}
	/**
	 * Get a single provider without related stations
	 * @param key identifying the provider
	 * @return a bike sharing service provider 
	 * @throws Exception
	 */
	
	public Provider getProviderByKey(ProviderKey key) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Provider p = null;
		try {
			pstm = conn.prepareStatement(STM_GETPROVIDERBYKEY);
			pstm.setString(1, key.getValue());
			rs = pstm.executeQuery();
			if (rs.next()) {
				p = new Provider();
				p.setKey(ProviderKey.valueOf(rs.getString("PKEY")));
				p.setName(rs.getString("NAME"));
				p.setWebsite(rs.getString("WEBSITE"));
				String type = rs.getString("TYPE");
				p.setType( ProviderType.clearchannel.getValue().equals(type) ? ProviderType.clearchannel : ProviderType.jcdecaux );
				p.setLocation(rs.getString("LOCATION"));
				p.setZoomLevel(rs.getInt("ZOOMLEVEL"));
				p.setLatitude(rs.getDouble("LATITUDE"));
				p.setLongitude(rs.getDouble("LONGITUDE"));
				p.setLastUpdate(rs.getLong("LASTUPDATE"));
			} else {
				return null;
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstm != null)
				pstm.close();
			releaseConnection();
		}
		
		return p;		
	}
	/**
	 * Get a list of all the providers without related stations
	 * @return a list of bike sharing service providers
	 * @throws Exception
	 */
	public List<Provider> getAllProviders() throws Exception {
		getConnection();
		ResultSet rs = null;
		Statement stm = null;
		Provider p = null;
		List<Provider> providers = new ArrayList<Provider>();
		try {
			stm = conn.createStatement();
			rs = stm.executeQuery(STM_GETALLPROVIDERS);
			while (rs.next()) {
				p = new Provider();
				p.setKey(ProviderKey.valueOf(rs.getString("PKEY")));
				p.setName(rs.getString("NAME"));
				p.setWebsite(rs.getString("WEBSITE"));
				String type = rs.getString("TYPE");
				p.setType( ProviderType.clearchannel.getValue().equals(type) ? ProviderType.clearchannel : ProviderType.jcdecaux );
				p.setLocation(rs.getString("LOCATION"));
				p.setZoomLevel(rs.getInt("ZOOMLEVEL"));
				p.setLatitude(rs.getDouble("LATITUDE"));
				p.setLongitude(rs.getDouble("LONGITUDE"));
				p.setLastUpdate(rs.getLong("LASTUPDATE"));
				providers.add(p);
			} 
		} finally {
			if (rs != null)
				rs.close();
			if (stm != null)
				stm.close();
			releaseConnection();

		}
		return providers;		
	}
	
	
	
	
	
	/**
	 * insert a new bike sharing service provider
	 * @param p a Provider
	 * @return a reference to the new inserted privider
	 * @throws Exception
	 */
	public Provider insertProvider(Provider p) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		try {
			
			pstm = conn.prepareStatement(STM_INSERTPROVIDER);
			pstm.setString(1, p.getKey().getValue());
			pstm.setLong(2, p.getLastUpdate());
			pstm.setString(3, p.getName());
			pstm.setString(4, p.getWebsite());
			pstm.setString(5, p.getType().getValue());
			pstm.setString(6, p.getLocation());
			pstm.setInt(7, p.getZoomLevel());
			pstm.setDouble(8, p.getLatitude());
			pstm.setDouble(9, p.getLongitude());
			pstm.execute();
		} catch (Throwable t) {
			if (conn != null)
				conn.rollback();
		} finally {
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return p;
	}
	public Station getStationByKey(ProviderKey pkey, String skey) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		Station s = null;
		try {
			pstm = conn.prepareStatement(STM_GETSTATIONBYKEY);
			pstm.setString(1, skey);
			pstm.setString(2, pkey.getValue());
			rs = pstm.executeQuery();
			if (rs.next()) {
				s = new Station();
				s.setId(rs.getInt("ID"));
				s.setKey(rs.getString("SKEY"));
				s.setProviderKey(ProviderKey.valueOf(rs.getString("PKEY")));
				s.setLocation(rs.getString("LOCATION"));
				s.setLatitude(rs.getDouble("LATITUDE"));
				s.setLongitude(rs.getDouble("LONGITUDE"));
				s.setLastUpdate(rs.getLong("LASTUPDATE"));
				pstm = conn.prepareStatement(STM_GETLASTDATA);
				pstm.setString(1, skey);
				pstm.setString(2, pkey.getValue());
				rs = pstm.executeQuery();
				if (rs.next()) {
					int bikes = rs.getInt("BIKES");
					int slots = rs.getInt("SLOTS");
					boolean available = rs.getBoolean("AVAILABLE");
					long lastUpdate = rs.getLong("LASTUPDATE");
					s.getData().add(
							new StationData(skey, pkey, bikes, slots,
									available, lastUpdate));
				}
			} else {
				return null;
			}
		} finally {
			if (rs != null)
				rs.close();
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return s;
	}
	public List<Station> getStationsByProvider(ProviderKey pkey) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		Station s = null;
		List<Station> stations = new ArrayList<Station>();
		try {
			pstm = conn.prepareStatement(STM_GETSTATIONSBYPROVIDER);
			pstm.setString(1, pkey.getValue());
			rs = pstm.executeQuery();
			
			while (rs.next()) {
				s = new Station();
				s.setId(rs.getInt("ID"));
				s.setKey(rs.getString("SKEY"));
				s.setProviderKey(ProviderKey.valueOf(rs.getString("PKEY")));
				s.setLocation(rs.getString("LOCATION"));
				s.setLatitude(rs.getDouble("LATITUDE"));
				s.setLongitude(rs.getDouble("LONGITUDE"));
				s.setLastUpdate(rs.getLong("LASTUPDATE"));
				pstm = conn.prepareStatement(STM_GETLASTDATA);
				pstm.setString(1, s.getKey());
				pstm.setString(2, pkey.getValue());
				rs2 = pstm.executeQuery();
				if (rs2.next()) {
					int bikes = rs2.getInt("BIKES");
					int slots = rs2.getInt("SLOTS");
					boolean available = rs2.getBoolean("AVAILABLE");
					long lastUpdate = rs2.getLong("LASTUPDATE");
					s.getData().add(
							new StationData(s.getKey(), pkey, bikes, slots,
									available, lastUpdate));
				}
				stations.add(s);
			} 
		} finally {
			if (rs != null)
				rs.close();
			if (rs2 != null)
				rs2.close();			
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return stations;		
	}
	
	public StationData insertStationData(StationData sd) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(STM_INSERTSTATIONDATA);
			pstm.setString(1, sd.getStationKey());
			pstm.setString(2, sd.getProviderKey().getValue());
			pstm.setInt(3, sd.getBikes());
			pstm.setInt(4, sd.getSlots());
			pstm.setBoolean(5, sd.isAvailable());
			pstm.setLong(6, sd.getLastUpdate());
			pstm.execute();
			//TODO get generated keys ???
		} finally {
			if (pstm != null)
				pstm.close();
			releaseConnection();
		}
		return sd;
	}
	public Station updateStation(Station s) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(STM_UPDATESTATION);
			pstm.setLong(1, s.getLastUpdate());
			pstm.setString(2, s.getKey());
			pstm.setString(3, s.getProviderKey().getValue());
			pstm.execute();
		} finally {
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return s;
	}

	public Provider updateProvider(Provider p) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		try {
			pstm = conn.prepareStatement(STM_UPDATEPROVIDER);
			pstm.setLong(1, p.getLastUpdate());
			pstm.setString(2, p.getKey().getValue());
			pstm.execute();
		} finally {
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return p;
	}
	
	public Station insertStation(Station s) throws Exception {
		getConnection();
		PreparedStatement pstm = null;
		try {
		
			
			pstm = conn.prepareStatement(STM_INSERTSTATION);
			pstm.setString(1, s.getKey());
			pstm.setString(2, s.getProviderKey().getValue());
			pstm.setLong(3, s.getLastUpdate());
			pstm.setString(4, s.getLocation());
			pstm.setDouble(5, s.getLatitude());
			pstm.setDouble(6, s.getLongitude());

			pstm.execute();
		} finally {
			if (pstm != null)
				pstm.close();
			releaseConnection();

		}
		return s;
	}
	public List<Provider> refreshProviders(String lastUpdate) throws Exception {
		getConnection();
		ResultSet rs = null;
		PreparedStatement stm = null;
		Provider p = null;
		List<Provider> providers = new ArrayList<Provider>();
		try {
			stm = conn.prepareStatement(STM_REFRESH_PROVIDERS);
			stm.setLong(1, Long.parseLong(lastUpdate));
			rs = stm.executeQuery();
			while (rs.next()) {
				p = new Provider();
				p.setKey(ProviderKey.valueOf(rs.getString("PKEY")));
				p.setName(rs.getString("NAME"));
				p.setWebsite(rs.getString("WEBSITE"));
				String type = rs.getString("TYPE");
				p.setType( ProviderType.clearchannel.getValue().equals(type) ? ProviderType.clearchannel : ProviderType.jcdecaux );
				p.setLocation(rs.getString("LOCATION"));
				p.setZoomLevel(rs.getInt("ZOOMLEVEL"));
				p.setLatitude(rs.getDouble("LATITUDE"));
				p.setLongitude(rs.getDouble("LONGITUDE"));
				p.setLastUpdate(rs.getLong("LASTUPDATE"));
				providers.add(p);
			} 
		} finally {
			if (rs != null)
				rs.close();
			if (stm != null)
				stm.close();
			releaseConnection();

		}
		return providers;		
	}
	public void login(String username, String passwd) throws Exception {
		getConnection();
		ResultSet rs = null;
		PreparedStatement stm = null;
		try {
			stm = conn.prepareStatement(STM_LOGIN);
			stm.setString(1, username);
			rs = stm.executeQuery();
			if (rs.next()) {
				String passDb = rs.getString("PASSWD");
				if (!passDb.equals(passwd))
					throw new Exception("invalid password !");
				return;
			} else {
				throw new Exception("user not found !");
			}
			
		} finally {
			if (rs != null)
				rs.close();
			if (stm != null)
				stm.close();
			releaseConnection();

		}
	}
}
